﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using Maticsoft.DBUtility;
using EnvironmentManagerSystem.MODE;

namespace EnvironmentManagerSystem.BLL
{
    public class Expert
    {
        #region 添加专家2012-12-20
        public static int Add(ExpertData model)
        {
            try
            {
                string sql = "insert into tbs001_expert(Name,Sex,Title,Birthday,CardNo,Work,Mobile,Phone,Fax,Email,NowWork,ExpertType,BusinessType,EIAType,Recommendation,ExpertMemo,ImageURL) values('" + model.Name + "','" + model.Sex + "','" + model.Title + "','" + model.Birthday + "','" + model.CardNo + "','" + model.Work + "','" + model.Mobile + "','" + model.Phone + "','" + model.Fax + "','" + model.Email + "','" + model.NowWork + "','" + model.ExpertType + "','" + model.BusinessType + "','" + model.EIAType + "','" + model.Recommendation + "','" + model.ExpertMemo + "','" + model.ImageURL + "')";
                return DbHelperMySQL.ExecuteSql(sql);
            }
            catch (Exception ex)
            {
                return 0;
            }
        }
        #endregion

        #region 修改专家012-12-20
        public static int Update(ExpertData model)
        {
            try
            {
                StringBuilder br = new StringBuilder();
                br.Append(" update tbs001_expert set");
                br.AppendFormat("  Name = '{0}', ", model.Name);
                br.AppendFormat("  Sex = '{0}', ", model.Sex);
                br.AppendFormat("  Title = '{0}', ", model.Title);
                br.AppendFormat("  Birthday = '{0}', ", model.Birthday);
                br.AppendFormat("  CardNo = '{0}', ", model.CardNo);
                br.AppendFormat("  Work = '{0}', ", model.Work);
                br.AppendFormat("  Mobile = '{0}', ", model.Mobile);
                br.AppendFormat("  Phone = '{0}', ", model.Phone);
                br.AppendFormat("  Fax = '{0}', ", model.Fax);
                br.AppendFormat("  Email = '{0}', ", model.Email);
                br.AppendFormat("  NowWork = '{0}', ", model.NowWork);
                br.AppendFormat("  ExpertType = '{0}', ", model.ExpertType);
                br.AppendFormat("  BusinessType = '{0}', ", model.BusinessType);
                br.AppendFormat("  EIAType = '{0}', ", model.EIAType);
                br.AppendFormat("  Recommendation = '{0}', ", model.Recommendation);
                br.AppendFormat("  ExpertMemo = '{0}', ", model.ExpertMemo);
                br.AppendFormat("  ImageURL = '{0}' ", model.ImageURL);
                br.AppendFormat(" where id = {0} ", model.ID);
                return DbHelperMySQL.ExecuteSql(br.ToString()) > 0 ? 1 : 0;
            }
            catch (Exception ex)
            {
                return 0;
            }
        }
        #endregion


        /// <summary>
        /// 根据名称删除主类
        /// </summary>
        /// <param name="name">要删除的主类名称</param>
        /// <returns>影响行数</returns>
        public static int DelByID(string id)
        {
            try
            {
                string sql = "delete from tbs001_expert where id=" + id;
                return DbHelperMySQL.ExecuteSql(sql);

            }
            catch (Exception ex)
            {
                return 0;
            }
        }
        /// <summary>
        /// 取出全部专家数据
        /// </summary>
        /// <returns>专家集合</returns>
        public static ExpertDataCollection GetList()
        {
            try
            {
                ExpertDataCollection lst = new ExpertDataCollection();
                StringBuilder br = new StringBuilder();
                br.Append(" select ");
                br.Append(" tbs001_expert.id, ");
                br.Append(" tbs001_expert.Name, ");
                br.Append(" tbs001_expert.Sex,  ");
                br.Append(" tbs001_expert.Title,  ");
                br.Append(" tbs001_expert.Birthday,  ");
                br.Append(" tbs001_expert.CardNo,  ");
                br.Append(" tbs001_expert.Work,  ");
                br.Append(" tbs001_expert.Mobile,  ");
                br.Append(" tbs001_expert.Phone,  ");
                br.Append(" tbs001_expert.Fax,  ");
                br.Append(" tbs001_expert.Email,  ");
                br.Append(" tbs001_expert.NowWork,  ");
                br.Append(" tbs001_expert.ExpertType,  ");
                br.Append(" tbs001_expert.BusinessType,  ");
                br.Append(" tbs001_expert.EIAType,  ");
                br.Append(" tbs001_expert.Recommendation,  ");
                br.Append(" tbs001_expert.ExpertMemo  ");
                br.Append(" from tbs001_expert");

                DataTable dt = DbHelperMySQL.Query(br.ToString()).Tables[0];
                for (int i = 0; i < dt.Rows.Count; i++)
                {
                    ExpertData model = new ExpertData();
                    model.ID = Convert.ToInt32(dt.Rows[i].ItemArray[0]);
                    model.Name = dt.Rows[i].ItemArray[1].ToString();
                    model.Sex = dt.Rows[i].ItemArray[2].ToString();
                    model.Title = dt.Rows[i].ItemArray[3].ToString();
                    model.Birthday = dt.Rows[i].ItemArray[4].ToString();
                    model.CardNo = dt.Rows[i].ItemArray[5].ToString();
                    model.Work = dt.Rows[i].ItemArray[6].ToString();
                    model.Mobile = dt.Rows[i].ItemArray[7].ToString();
                    model.Phone = dt.Rows[i].ItemArray[8].ToString();
                    model.Fax = dt.Rows[i].ItemArray[9].ToString();
                    model.Email = dt.Rows[i].ItemArray[10].ToString();
                    model.NowWork = dt.Rows[i].ItemArray[11].ToString();
                    model.ExpertType = dt.Rows[i].ItemArray[12].ToString();
                    model.BusinessType = dt.Rows[i].ItemArray[13].ToString();
                    model.EIAType = dt.Rows[i].ItemArray[14].ToString();
                    model.Recommendation = dt.Rows[i].ItemArray[15].ToString();
                    model.ExpertMemo = dt.Rows[i].ItemArray[16].ToString();
                    lst.Add(model);
                }
                return lst;
            }
            catch (Exception ex)
            {
                Console.Out.WriteLine(ex.Message);
                return null;
            }
        }
        /// <summary>
        /// 取出全部专家数据
        /// </summary>
        /// <returns>专家集合</returns>
        public static DataTable GetListTable()
        {
            try
            {
                ExpertDataCollection lst = new ExpertDataCollection();
                StringBuilder br = new StringBuilder();
                br.Append(" select ");
                br.Append(" tbs001_expert.id, ");
                br.Append(" tbs001_expert.Name, ");
                br.Append(" tbs001_expert.Sex,  ");
                br.Append(" tbs001_expert.Title,  ");
                br.Append(" tbs001_expert.Birthday,  ");
                br.Append(" tbs001_expert.CardNo,  ");
                br.Append(" tbs001_expert.Work,  ");
                br.Append(" tbs001_expert.Mobile,  ");
                br.Append(" tbs001_expert.Phone,  ");
                br.Append(" tbs001_expert.Fax,  ");
                br.Append(" tbs001_expert.Email,  ");
                br.Append(" tbs001_expert.NowWork,  ");
                br.Append(" tbs001_expert.ExpertType,  ");
                br.Append(" tbs001_expert.BusinessType,  ");
                br.Append(" tbs001_expert.EIAType,  ");
                br.Append(" tbs001_expert.Recommendation,  ");
                br.Append(" tbs001_expert.ExpertMemo  ");
                br.Append(" from tbs001_expert");

                DataTable dt = DbHelperMySQL.Query(br.ToString()).Tables[0];

                return dt;
            }
            catch (Exception ex)
            {
                Console.Out.WriteLine(ex.Message);
                return null;
            }
        }

        /// <summary>
        /// 取出全部专家数据
        /// </summary>
        /// <returns>专家集合</returns>
        public static ExpertDataCollection GetModelByID(string id)
        {
            try
            {
                ExpertDataCollection lst = new ExpertDataCollection();
                StringBuilder br = new StringBuilder();
                br.Append(" select ");
                br.Append(" tbs001_expert.id, ");
                br.Append(" tbs001_expert.Name, ");
                br.Append(" tbs001_expert.Sex,  ");
                br.Append(" tbs001_expert.Title,  ");
                br.Append(" tbs001_expert.Birthday,  ");
                br.Append(" tbs001_expert.CardNo,  ");
                br.Append(" tbs001_expert.Work,  ");
                br.Append(" tbs001_expert.Mobile,  ");
                br.Append(" tbs001_expert.Phone,  ");
                br.Append(" tbs001_expert.Fax,  ");
                br.Append(" tbs001_expert.Email,  ");
                br.Append(" tbs001_expert.NowWork,  ");
                br.Append(" tbs001_expert.ExpertType,  ");
                br.Append(" tbs001_expert.BusinessType,  ");
                br.Append(" tbs001_expert.EIAType,  ");
                br.Append(" tbs001_expert.Recommendation,  ");
                br.Append(" tbs001_expert.ExpertMemo,  ");
                br.Append(" tbs001_expert.ImageURL  ");
                br.Append(" from tbs001_expert");
                br.AppendFormat(" where tbs001_expert.id={0}", id);
                DataTable dt = DbHelperMySQL.Query(br.ToString()).Tables[0];
                for (int i = 0; i < dt.Rows.Count; i++)
                {
                    ExpertData model = new ExpertData();
                    model.ID = Convert.ToInt32(dt.Rows[i].ItemArray[0]);
                    model.Name = dt.Rows[i].ItemArray[1].ToString();
                    model.Sex = dt.Rows[i].ItemArray[2].ToString();
                    model.Title = dt.Rows[i].ItemArray[3].ToString();
                    model.Birthday = dt.Rows[i].ItemArray[4].ToString();
                    model.CardNo = dt.Rows[i].ItemArray[5].ToString();
                    model.Work = dt.Rows[i].ItemArray[6].ToString();
                    model.Mobile = dt.Rows[i].ItemArray[7].ToString();
                    model.Phone = dt.Rows[i].ItemArray[8].ToString();
                    model.Fax = dt.Rows[i].ItemArray[9].ToString();
                    model.Email = dt.Rows[i].ItemArray[10].ToString();
                    model.NowWork = dt.Rows[i].ItemArray[11].ToString();
                    model.ExpertType = dt.Rows[i].ItemArray[12].ToString();
                    model.BusinessType = dt.Rows[i].ItemArray[13].ToString();
                    model.EIAType = dt.Rows[i].ItemArray[14].ToString();
                    model.Recommendation = dt.Rows[i].ItemArray[15].ToString();
                    model.ExpertMemo = dt.Rows[i].ItemArray[16].ToString();
                    model.ImageURL = dt.Rows[i].ItemArray[17].ToString();
                    lst.Add(model);
                }
                return lst;
            }
            catch (Exception ex)
            {
                Console.Out.WriteLine(ex.Message);
                return null;
            }
        }

        #region 按拼音排序查询专家(x)
        public static DataTable GetAll()
        {
            try
            {
                StringBuilder br = new StringBuilder();
                //（1）mysql中文排序
                //数据库采用utf-8编码，order by 无法对中文按照拼音排序 ，对中文字段转为GBK后排序即可。
                //SELECT * FROM dept ORDER BY CONVERT( fd_dept_name USING gbk ) ;
                //（2）
                br.Append("SELECT * FROM tbs001_expert ORDER BY CONVERT(NAME USING gbk)");
                return DbHelperMySQL.Query(br.ToString()).Tables[0];
            }
            catch { return null; }
        }
        #endregion


        #region 专家搜索条件(按专家姓名)
        public static DataTable GetSelectName(string Name, string Work)
        {
            try
            {
                string strPJ = "";
                if (Name == "")//专家姓名
                {
                }
                else
                {
                    strPJ += " AND tbs001_expert.NAME like '%" + Name + "%'";
                }
                if (Work == "")//工作单位
                {
                }
                else
                {
                    strPJ += " AND tbs001_expert.Work like '%" + Work + "%'";
                }
                StringBuilder brb = new StringBuilder();
                brb.AppendFormat(@"SELECT tbs001_expert.id,tbs001_expert.NAME,tbs001_expert.Sex,tbs001_expert.Title,tbs001_expert.Birthday,tbs001_expert.CardNo,tbs001_expert.WORK,tbs001_expert.Mobile,tbs001_expert.Phone,tbs001_expert.Fax,tbs001_expert.Email,tbs001_expert.NowWork,tbs001_expert.ExpertType,tbs001_expert.BusinessType,tbs001_expert.EIAType,tbs001_expert.Recommendation,tbs001_expert.ExpertMemo FROM tbs001_expert");
                brb.AppendFormat(" WHERE 1=1 " + strPJ + " ");
                return DbHelperMySQL.Query(brb.ToString()).Tables[0];
            }
            catch { return null; }
        }
        #endregion

    }
}
